import time

# 映射表中批量添加角色
# update dbo.autoops_hrmapapi SET API_ROLES=API_ROLES+';Iwork-年度合理化建议'

# 为派遣工提取今日入职sql查询提供时间格式
time_str = time.strftime("%Y-%m-%d 00:00:00.000", time.localtime())
# time_str = '2020-12-28 00:00:00.000'

sqlcmd_NewComerToday = '''SELECT cWorkNo,cName,cPrimaryDept,cDeptName,cPosition FROM dbo.v_autodisp_NewComerToday'''

# 该sql语句供测试使用（使用账号为oa，库为‘FE_BSEE5’）
# sqlcmd_NewComerToday = '''SELECT cWorkNo,cName,cPrimaryDept,cDeptName,cPosition FROM LS_DB1_hriteroa.hr.dbo.Employee WHERE rEnterDate='{}' '''.format(time_str)

sqlcmd_NewDispatchComerToday = '''
    SELECT cWorkNo,cName,cPrimaryDept,cDeptName,cPosition FROM dbo.Employee_Dispatch WHERE rEnterDate='{}'
'''.format(time_str)

# sqlcmd_NewDispatchComerToday = '''
# SELECT cWorkNo,cName,cPrimaryDept,cDeptName,cPosition FROM dbo.Employee_Dispatch where cWorkNo IN ('65579','66650','66651','66652','66680','66679')
# '''


sqlcmd_queryworkno = ''' SELECT UserId FROM dbo.Sys_User  where UserId='{}' AND IsDel=0'''

sqlcmd_insertapi = '''
        INSERT INTO dbo.Sys_User
        (
          ID,
          UserId,
          UserName,
          PassWord,
          sex,
          org,
          CreateUserID,
          CreateUserName,
          CreateTime,
          UpdateUserID,
          UpdateUserName,
          UpdateTime,
          IsUsed,
          IsDel,
          PostID,
          PostName,
          Lead_UserID,
          Para,
          IsOnlyAllowDomainLogin,
          IsReSetPwd,
          ReSetPwdTime,
          PhoneNumber
        )
      VALUES
    (   NEWID(),      -- ID - varchar(50)
          '{}',        -- UserId - varchar(50)
          '{}',        -- UserName - varchar(50)
          'E10ADC39LE49BA59ADBE56E0573CF20F883E==',         -- PassWord - varchar(50)
          '',           -- Sex - varchar(50)
          '{}',         -- Org - varchar(50)
          'autoops',        -- CreateUserID - varchar(50)
          '入职人员自动添加',        -- CreateUserName - varchar(50)
          GETDATE(),       -- CreateTime - datetime
          'autoops',        -- UpdateUserID - varchar(50)
          '入职人员自动添加',        -- UpdateUserName - varchar(50)
          GETDATE(),      -- UpdateTime - datetime
          1,      -- IsUsed - bit
          0,      -- IsDel - bit
          '{}',       -- PostID - varchar(500)
          '{}',       -- PostName - varchar(500)
          '{}',       -- Lead_UserID - varchar(50)
          '',        -- Para - varchar(50)
          0,      -- IsOnlyAllowDomainLogin - bit
          0,      -- IsReSetPwd - bit
          null,   -- ReSetPwdTime - datetime
          ''         -- PhoneNumber - varchar(50)
          )
        '''

sqlcmd_isBindRecur = '''
        SELECT * FROM dbo.Sys_UserRole WHERE UserId='{}' AND RoleID='{}'
    '''

sqlcmd_insertAPIrole = '''
        INSERT INTO dbo.Sys_UserRole
        (
        UserId,
        RoleID,
        Source
        )
        VALUES
        (   
        '{}', -- UserId - varchar(50)
        '{}', -- RoleID - varchar(50)
        0   -- Source - int
        )
    '''

sqlcmd_queryapi = '''
    SELECT API_DEPTNAME,API_POSITION FROM dbo.autoops_hrmapapi WHERE ID='{}'
'''

sqlcmd_queryDeptID = '''
    SELECT deptId FROM dbo.v_org_info a INNER JOIN dbo.Sys_Org b ON a.deptId=b.ID WHERE deptName = '{}' AND b.IsDel=0
'''

sqlcmd_queryPostID = '''
        SELECT  b.OrgID + '|' + a.ID AS ID
        FROM dbo.Sys_Post a 
        INNER JOIN dbo.Sys_OrgPost b ON a.id=b.PostID 
        WHERE b.OrgID='{}' AND a.name='{}' AND a.IsDel=0
        '''

sqlcmd_queryLeaderID = '''
    SELECT API_LEADERID FROM dbo.autoops_hrmapapi WHERE ID='{}'
'''

sqlcmd_queryUserID = '''
        SELECT ID FROM dbo.Sys_User WHERE UserId='{}' AND IsDel=0
    '''

sqlcmd_queryAPIrole = '''
        SELECT API_ROLES FROM dbo.autoops_hrmapapi WHERE id='{}'
    '''

sqlcmd_queryApiRoleID = '''
            SELECT ID FROM dbo.Sys_Role WHERE Name='{}' AND IsDel=0
        '''